unit EmployeeForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.StdCtrls, Vcl.Grids, IB,
  Vcl.DBGrids, Vcl.DBCtrls, Vcl.Mask, Vcl.ComCtrls, DBDateTimePicker, StrUtils,
  UserUtils;

type
  TfmEmployeeInfo = class(TForm)
    DBGrid1: TDBGrid;
    Label1: TLabel;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    txtFirstName: TEdit;
    txtLastName: TEdit;
    txtIC: TEdit;
    txtPassport: TEdit;
    dtJoinIn: TDateTimePicker;
    dtDOB: TDateTimePicker;
    cbGender: TComboBox;
    mmRemark: TMemo;
    btnNew: TButton;
    btnEdit: TButton;
    btnSave: TButton;
    btnClose: TButton;
    Label12: TLabel;
    txtBasicSalary: TEdit;
    btnDelete: TButton;
    chkEPF: TCheckBox;
    chkSOCSO: TCheckBox;
    dbcbMarriageStatus: TDBLookupComboBox;
    dbcbPosition: TDBLookupComboBox;
    procedure DBGrid1CellClick(Column: TColumn);
    procedure btnCloseClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnDeleteClick(Sender: TObject);
    procedure txtICKeyPress(Sender: TObject; var Key: Char);
    procedure txtBasicSalaryKeyPress(Sender: TObject; var Key: Char);
    procedure txtBasicSalaryExit(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
    Operation: TOperation;
    CurrentEmployeeID: integer;
    procedure FillDetails;
    procedure ClearDetails;
    procedure InsertDetails;
    procedure UpdateDetails;
  public
    { Public declarations }
  end;

var
  fmEmployeeInfo: TfmEmployeeInfo;

implementation

{$R *.dfm}

procedure TfmEmployeeInfo.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmEmployeeInfo.DBGrid1CellClick(Column: TColumn);
begin
  btnEdit.Click;
end;

procedure TfmEmployeeInfo.DBGrid1TitleClick(Column: TColumn);
begin
  DataModule1.fbEmployee.IndexFieldNames := Column.FieldName;
end;

procedure TfmEmployeeInfo.FillDetails;
var
  i: integer;
begin
  CurrentEmployeeID := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
  txtFirstName.Text := StringReplace(DBGrid1.DataSource.DataSet.FieldByName('FIRST_NAME').AsString, '''''', '''', [rfReplaceAll, rfIgnoreCase]);
  txtLastName.Text := StringReplace(DBGrid1.DataSource.DataSet.FieldByName('LAST_NAME').AsString, '''''', '''', [rfReplaceAll, rfIgnoreCase]);
  txtIC.Text := StringReplace(DBGrid1.DataSource.DataSet.FieldByName('IC_NUMBER').AsString, '''''', '''', [rfReplaceAll, rfIgnoreCase]);
  txtPassport.Text := StringReplace(DBGrid1.DataSource.DataSet.FieldByName('PASSPORT_NUMBER').AsString, '''''', '''', [rfReplaceAll, rfIgnoreCase]);
  dbcbPosition.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('POSITION').AsString;
  dbcbMarriageStatus.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('MARRIAGE_STATUS').AsString;
  dtJoinIn.DateTime := DBGrid1.DataSource.DataSet.FieldByName('START_SERVICE_DATE').AsDateTime;
  dtDOB.DateTime := DBGrid1.DataSource.DataSet.FieldByName('DOB').AsDateTime;
  txtBasicSalary.Text := Format('%.2f', [DBGrid1.DataSource.DataSet.FieldByName('BASE_PAY').AsFloat]);
  mmRemark.Lines.Text := StringReplace(DBGrid1.DataSource.DataSet.FieldByName('REMARK').AsString, '''''', '''', [rfReplaceAll, rfIgnoreCase]);
  chkEPF.Checked := DBGrid1.DataSource.DataSet.FieldByName('EPF').AsString = '1';
  chkSOCSO.Checked := DBGrid1.DataSource.DataSet.FieldByName('SOCSO').AsString = '1';
  for i := 0 to cbGender.Items.Count - 1 do
    if LeftStr(cbGender.Items.Strings[i], 1) = DBGrid1.DataSource.DataSet.FieldByName('GENDER').AsString then
      cbGender.ItemIndex := i;
  Operation := opEdit;
end;

procedure TfmEmployeeInfo.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmEmployeeInfo);
end;

procedure TfmEmployeeInfo.FormCreate(Sender: TObject);
begin
  SetLength(TrueBoolStrs, 1);
  SetLength(FalseBoolStrs, 1);
  TrueBoolStrs[0] := '1';
  FalseBoolStrs[0] := '0';
  Operation := opNew;
  cbGender.ItemIndex := -1;
  with DataModule1 do
  begin
    if not fbJobPosition.Active then
      fbJobPosition.Open;
    if not fbMarriageStatus.Active then
      fbMarriageStatus.Open;
    if not fbEmployee.Active then
      fbEmployee.Open;
  end;
end;

procedure TfmEmployeeInfo.FormShow(Sender: TObject);
begin
  with DataModule1 do
  begin
    fbMarriageStatus.Last;
    fbMarriageStatus.First;
    fbJobPosition.Last;
    fbJobPosition.First;
  end;
end;

procedure TfmEmployeeInfo.btnDeleteClick(Sender: TObject);
begin
  if not DataModule1.fbEmployee.Eof and not DataModule1.fbEmployee.Bof then
  begin
    if MessageDlg('Confirm delete?', mtWarning, mbYesNo, 0) = mrYes then
    begin
      try
        with DataModule1.qrySQL do begin
          SQL.Clear;
          Params.Clear;
          try
            SQL.Add('delete from EMPLOYEE where ID=:ID');
            Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
            if not Prepared then
              Prepare;
            ExecSQL;
            Transaction.Commit;
          except
            on E: EIBInterBaseError do
            begin
              Transaction.Rollback;
              ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
            end;
          end;
        end;
      finally
        with DataModule1.fbEmployee do
          if Active then
            Refresh
          else
            Active := True;
        ClearDetails;
      end;
    end;
  end else
    MessageBox(Self.WindowHandle, 'No record selected!', 'Warning', 0);
end;

procedure TfmEmployeeInfo.btnEditClick(Sender: TObject);
begin
  FillDetails;
end;

procedure TfmEmployeeInfo.btnNewClick(Sender: TObject);
begin
  ClearDetails;
end;

procedure TfmEmployeeInfo.btnSaveClick(Sender: TObject);
begin
  if Operation = opNew then
    InsertDetails
  else if Operation = opEdit then
    UpdateDetails;
  ClearDetails;
end;

procedure TfmEmployeeInfo.ClearDetails;
begin
  txtFirstName.Text := '';
  txtLastName.Text := '';
  txtIC.Text := '';
  txtPassport.Text := '';
  cbGender.ItemIndex := -1;
  dbcbPosition.KeyValue := '';
  dbcbMarriageStatus.KeyValue := '';
  dtJoinIn.DateTime := Now;
  dtDOB.DateTime := Now;
  txtBasicSalary.Text := '0';
  mmRemark.Lines.Clear;
  chkEPF.Checked := False;
  chkSOCSO.Checked := False;
  Operation := opNew;
end;

procedure TfmEmployeeInfo.InsertDetails;
begin
  try
    with DataModule1.qrySQL do begin
      SQL.Clear;
      Params.Clear;
      SQL.Add('insert into EMPLOYEE (NAME, FIRST_NAME, LAST_NAME, GENDER, DOB, ' +
              'START_SERVICE_DATE, "POSITION", BASE_PAY, MARRIAGE_STATUS, IC_NUMBER, ' +
              'PASSPORT_NUMBER, EPF, SOCSO, REMARK, CREATE_DATE, UPDATE_DATE) values (' +
              ':Name, :FirstName, :LastName, :Gender, :DOB, :DateJoinIn, ' +
              ':Position, :BasePay, :MarriageStatus, :IC, :Passport, :EPF, :SOCSO, ' +
              ':Remark, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)');
      try
        Params.ParamByName('Name').AsString := txtFirstName.Text + ' ' + txtLastName.Text;
        Params.ParamByName('FirstName').AsString := txtFirstName.Text;
        Params.ParamByName('LastName').AsString := txtLastName.Text;
        Params.ParamByName('IC').AsString := txtIC.Text;
        Params.ParamByName('Passport').AsString := txtPassport.Text;
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        Params.ParamByName('Gender').AsString := LeftStr(PChar(cbGender.Text), 1);
        Params.ParamByName('Position').AsString := dbcbPosition.KeyValue;
        Params.ParamByName('MarriageStatus').AsString := dbcbMarriageStatus.KeyValue;
        Params.ParamByName('DOB').AsDate := dtDOB.Date;
        Params.ParamByName('DateJoinIn').AsDate := dtJoinIn.Date;
        Params.ParamByName('BasePay').AsCurrency := StrToFloat(txtBasicSalary.Text);
        Params.ParamByName('EPF').AsString := BoolToStr(chkEPF.Checked, True);
        Params.ParamByName('SOCSO').AsString := BoolToStr(chkSOCSO.Checked, True);
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    end;
  finally
    with DataModule1 do
    begin
      if not fbEmployee.Active then
        fbEmployee.Open;
      if not fbMarriageStatus.Active then
        fbMarriageStatus.Open;
      fbMarriageStatus.Last;
      fbMarriageStatus.First;
      if not fbJobPosition.Active then
        fbJobPosition.Open;
      fbJobPosition.Last;
      fbJobPosition.First;
    end;
  end;
end;

procedure TfmEmployeeInfo.txtBasicSalaryExit(Sender: TObject);
begin
  txtBasicSalary.Text := Format('%.2f', [StrToFloat(txtBasicSalary.Text)]);
end;

procedure TfmEmployeeInfo.txtBasicSalaryKeyPress(Sender: TObject;
  var Key: Char);
var
  i: integer;
begin
  case Key of
    '0'..'9':;
    '.':begin
           i := Pos('.', txtBasicSalary.Text);
           if i > 0 then
           begin
             Key := #0;
             txtBasicSalary.SelStart := i + 1;
             txtBasicSalary.SelLength := Length(txtBasicSalary.Text) - i;
           end;
        end;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmEmployeeInfo.txtICKeyPress(Sender: TObject; var Key: Char);
begin
  case Key of
    '0'..'9':;
    '-':;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

procedure TfmEmployeeInfo.UpdateDetails;
begin
  try
    with DataModule1.qrySQL do begin
      SQL.Clear;
      Params.Clear;
      SQL.Add('update EMPLOYEE set NAME=:Name, FIRST_NAME=:FirstName, LAST_NAME=:LastName, ' +
              'GENDER=:Gender, DOB=:DOB, START_SERVICE_DATE=:DateJoinin, "POSITION"=:Position, ' +
              'BASE_PAY=:BasePay, MARRIAGE_STATUS=:MarriageStatus, IC_NUMBER=:IC, ' +
              'PASSPORT_NUMBER=:Passport, EPF=:EPF, SOCSO=:SOCSO, REMARK=:Remark, ' +
              'UPDATE_DATE=CURRENT_TIMESTAMP where ID=:ID');
      try
        Params.ParamByName('ID').AsInteger := CurrentEmployeeID;
        Params.ParamByName('Name').AsString := txtFirstName.Text + ' ' + txtLastName.Text;
        Params.ParamByName('FirstName').AsString := txtFirstName.Text;
        Params.ParamByName('LastName').AsString := txtLastName.Text;
        Params.ParamByName('IC').AsString := txtIC.Text;
        Params.ParamByName('Passport').AsString := txtPassport.Text;
        Params.ParamByName('Remark').AsString := mmRemark.Lines.Text;
        Params.ParamByName('Gender').AsString := LeftStr(PChar(cbGender.Text), 1);
        Params.ParamByName('Position').AsString := dbcbPosition.KeyValue;
        Params.ParamByName('MarriageStatus').AsString := dbcbMarriageStatus.KeyValue;
        Params.ParamByName('DOB').AsDate := dtDOB.Date;
        Params.ParamByName('DateJoinin').AsDate := dtJoinIn.Date;
        Params.ParamByName('BasePay').AsCurrency := StrToFloat(txtBasicSalary.Text);
        Params.ParamByName('EPF').AsString := BoolToStr(chkEPF.Checked, True);
        Params.ParamByName('SOCSO').AsString := BoolToStr(chkSOCSO.Checked, True);
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    end;
  finally
    with DataModule1 do
    begin
      if not fbEmployee.Active then
        fbEmployee.Open;
      if not fbMarriageStatus.Active then
        fbMarriageStatus.Open;
      fbMarriageStatus.Last;
      fbMarriageStatus.First;
      if not fbJobPosition.Active then
        fbJobPosition.Open;
      fbJobPosition.Last;
      fbJobPosition.First;
    end;
  end;
end;

end.
